Mail Merge Data File (for pooled income funds) file layout
The file created by Mail Merge Data File under PIF > Tax Reporting is formatted for merging with a letter created in a word processor, such as Microsoft Word. The data included in the file depends on whether the file is produced For Participants or Only for Advisors Marked to Receive Copies.
You can choose between two text formats:
-
Comma and quote delimited (.csv): character values are enclosed in double quotes (e.g., "Joe Donor") and numbers, dollar amounts, and dates are not (e.g., 10/1/2005 or 120412). The fields included in the file and their order are shown below.
-
Fixed length text (.sdf): numbers and dollar amounts are right justified; character values and dates are left justified. The fields included in each record and their column positions are shown below.
This mail merge data file is available to Pooled Fund Organizer module clients only.
Mail Merge Data File created for participants
File records are sorted by organization nickname, then pooled income fund nickname, then name of participant. The fields included in each record in the mail merge data file are as follows:
|
Field Name |
Table.Field |
Name in File |
Position |
Description |
|
Organization ID |
tblOrganization.OrgID |
1-10 |
Organization Key set up by GiftWrap for its own use |
|
|
Organization Nickname |
tblOrganization.OrgNickname |
11-20 |
Nickname of organization |
|
|
Organization Name |
tblOrganization.OrgName |
21- |
Full name of organization |
|
|
PIF ID |
tblPif.PifID |
-80 |
Fund Key set by GiftWrap for its own use |
|
|
PIF Full Name |
tblPif.PifName |
81-130 |
Full name of fund |
|
|
PIF Nickname |
tblPif.PifNickname |
131-140 |
Nickname of fund |
|
|
PIF Tax ID# |
tblPif.PifTaxIDNumber |
141-150 |
Fund Tax ID# without a hyphen |
|
|
PIF Tax ID# |
fmtPifTaxIDNumber |
151-160 |
Fund Tax ID# including hyphen |
|
|
PIF Account 1 |
tblPif.PifAccount1 |
163-182 |
Fund Account 1 |
|
|
PIF Account 2 |
tblPif.PifAccount2 |
183-202 |
Fund Account 2 |
|
|
PIF Account 3 |
tblPif.PifAccount3 |
203-222 |
Fund Account 3 |
|
|
Fiduciary Name |
tblPif.MgrName |
223-272 |
|
|
|
Fund Contact |
tblPif.MgrContact |
273-322 |
Name of person to contact at fund; title optional |
|
|
Fund Contact Address Street 1 |
tblAddress.Street1 |
323-382 |
|
|
|
Fund Contact Address Street 2 |
Pif. tblAddress.Street12 |
383-442 |
|
|
|
Fund Contact City |
tblAddress.City |
443-462 |
|
|
|
Fund Contact State |
tblAddress.StateCode |
463-464 |
|
|
|
Fund Contact Zipcode |
tblAddress.Zipcode |
465-474 |
|
|
|
Fund Contact Phone |
tblAddress.Phone |
480-499 |
|
|
|
|
PifAddressLine1 |
500-569 |
line 1 of formatted contact address |
|
|
|
PifAddressLine2 |
570-639 |
line 2 of formatted contact address |
|
|
|
PifAddressLine3 |
640-709 |
line 3 of formatted contact address |
|
|
Gift key |
tblGift.GiftKey |
710-719 |
Gift Key |
|
|
Market Value |
tblGift.MarketValue |
720-737 |
Gift market value |
|
|
Market Value Date |
tblGift.MarketValueDate |
738-745 |
Gift market value date; mmddyyyy |
|
|
Total Amount |
tblGift.TotalGiftAmount |
746-765 |
Total amount of gift and any gift additions |
|
|
Total Units |
tblGift.TotalUnits |
766-782 |
Total units assigned to gift and any gift additions |
|
|
Person key |
tblPerson.PersonKey |
783-792 |
Participant's Person Key |
|
|
Person Prefix |
tblPerson.Prefix |
793-802 |
Participant's name prefix |
|
|
Person First Name |
tblPerson.FirstName |
803-822 |
Participant's first name |
|
|
Person Last Name |
tblPerson.LastName |
823- |
Participant's last name |
|
|
Person Suffix |
tblPerson.Suffix |
|
Participant's name suffix |
|
|
Person Salutation |
tblPerson.Salut |
858-887 |
Participant's salutation |
|
|
|
fmtFullName |
888-967 |
Participant's name, formatted (Prefix + FirstName + LastName + Suffix) |
|
|
Person SS# |
tblPerson.SSN |
968-976 |
Participant's social security # formatted without hyphens |
|
|
Person SS# |
fmtSSN |
978-988 |
Participant's social security # formatted with hyphens |
|
| Tax Year selected by user |
fmtTaxYear |
990-993 |
Tax year of income data |
|
|
Date for Document entered by user |
fmtLetterDate
|
994-1001 |
Date for Document; mmddyyyy |
|
| calFraction | 1002-1021 |
Fraction of total fund income distributed to participant |
||
|
|
calDivUS |
1022-1039 |
US interest reported as dividends |
|
|
|
calDivReg |
1040-1057 |
dividends reported as qualified dividends |
|
|
|
calDivNonQual |
1058-1075 |
dividends excluding US interest reported as qualified dividends and dividends reported as qualified dividends |
|
|
|
calDivTot |
1076-1093 |
total dividends: calDivUS + calDivReg + calDivNonQual |
|
|
|
calDivState |
1094-1111 |
total state dividends: calDivReg + calDivNonQual |
|
|
|
calDivIntUS |
1112-1129 |
|
|
|
|
calIntUS |
1130-1147 |
US interest |
|
|
|
calIntMABank |
1148-1165 |
MA bank interest |
|
|
|
calIntCorp |
1166-1183 |
corporate interest |
|
|
|
calIntState |
1184-1201 |
total state interest: calIntMABank + calIntCorp |
|
|
|
calIntTotal |
1202-1219 |
total interest: calIntMABank + calIntCorp + calIntUS |
|
|
|
calOthIncUS |
1220-1237 |
other income subject to US tax only |
|
|
|
calOthIncReg |
1238-1255 |
other income subject to state and US tax |
|
|
|
calOthIncTot |
1256-1273 |
total other income: calOthIncUS + calOthIncReg |
|
|
|
calTax |
1274-1291 |
foreign tax paid |
|
|
|
calAdjust |
1292-1309 |
adjustments |
|
|
|
calTxblState |
1310-1327 |
state taxable income: calIntMABank + calIntCorp + calDivReg + calDivNonQual + calOthIncReg |
|
|
|
calTxblUS |
1328-1345 |
income taxable by US only (not state): calDivUS + calIntUS + calOthIncUS |
|
|
|
calTxblFed |
1346-1363 |
US taxable income: calDivTot + calIntTotal + calOthIncTot |
|
|
|
calTotalTaxable |
1364-1381 |
Total taxable income received by participant during tax year |
|
|
calGrossDist |
1382-1399 |
Total gross payments made to participant during tax year |
||
|
|
calFederalWithheld |
1400-1417 |
Total federal withholding on all participant's payments made during tax year |
|
|
|
calStateWithheld |
1418-1435 |
Total state withholding on all participant's payments made during tax year |
|
|
|
calAnnualPayment |
1436-1453 |
Total amount distributed to the participant during tax year. |
|
|
|
tblAddressType.Short |
1454-1473 |
Type of address |
|
|
|
tblAddress.Street1 |
1474-1533 |
Participant's legal address street 1* |
|
|
|
tblAddress.Street2 |
1534-1593 |
Participant's legal address line 2* |
|
|
|
tblAddress.City |
1594-1613 |
Participant's legal address city* |
|
|
|
tblAddress.StateCode |
1614-1615 |
Participant's legal address state* |
|
|
|
tblAddress.Zipcode |
1616-1625 |
Participant's legal address zip code* |
|
|
|
tblAddress.Country |
1631-1650 |
Participant's legal address country* |
|
|
|
fmtCSZ |
1651-1730 |
Participant's legal address city, state, and zip formatted (City + ", " + State + " " + Zipcode) |
|
|
|
fmtLegalAddressLine1 |
1731-1800 |
Formatted first line of Participant's legal address* |
|
|
|
fmtLegalAddressLine2 |
1801-1870 |
Formatted second line of Participant's legal address* |
|
|
|
fmtLegalAddressLine3 |
1871-1940 |
Formatted third line of Participant's legal address* |
|
|
fmtLegalAddressLine4 |
1941-2010 |
Formatted fourth line of Participant's legal address* |
||
|
|
fmtDomestic |
2011 |
if person is not foreign, value = "X", otherwise blank |
|
|
|
fmtForeign |
2012 |
if person is foreign, value = "X", otherwise blank |
|
|
|
calFinal |
2013 |
if DeathDate <> Null and Year of DeathDate is between 1900 and TaxYear, set value to "X", otherwise value is blank |
Mail Merge Data File created for participant advisors marked to receive copies of tax forms
File records are sorted by organization nickname, then pooled income fund nickname, then name of participant. The fields included in each record in the mail merge data file are as follows:
|
Field Name |
Table.Field |
Name in File |
Position |
Description |
|
Advisor Prefix |
tblPerson.Prefix |
aprefix |
1-10 |
|
|
Advisor First Name |
tblPerson.FirstName |
afname |
11-30 |
|
|
Advisor Last Name |
tblPerson.LastName |
alname |
31-56 |
|
|
Advisor Suffix |
tblPerson.Suffix |
asuffix |
57-65 |
|
|
Advisor Salutation |
tblPerson.Salut |
asalut |
66-95 |
|
|
Advisor Address Line 1 |
tblAddress.Street1 |
aadradd1 |
96-155 |
|
|
Advisor Address Line 2 |
tblAddress.Street2 |
aadradd2 |
156-215 |
|
|
Advisor City |
tblAddress.City |
acity |
216-235 |
|
|
Advisor State |
tblAddress.StateCode |
astate |
236-237 |
|
|
Advisor Zipcode |
tblAddress.Zipcode |
azipcode |
238-247 |
|
|
Advisor Country |
tblAddress.Country |
acountry |
248-267 |
|
|
|
fmtAdvisorFullName |
fmtAdvisorFullName |
268-337 |
Advisor's full name (Prefix + " " + FirstName + " " + LastName + Suffix) |
|
|
|
fmtAdvisorAddressLine1 |
338-407 |
First line of advisor's address |
|
|
|
fmtAdvisorAddressLine2 |
408-477 |
Second line of advisor's address |
|
|
|
fmtAdvisorAddressLine3 |
478-547 |
Third line of advisor's address |
|
|
|
fmtAdvisorAddressLine4 |
548-617 |
Fourth line of advisor's address |
|
Person Key |
tblPerson.PersonKey |
person_pk |
618-632 |
|
|
Person Prefix |
tblPerson.Prefix |
prefix |
632-642 |
|
|
Person First Name |
tblPerson.FirstName |
fname |
643-662 |
|
|
Person Last Name |
tblPerson.LastName |
lname |
663-687 |
|
|
Person Suffix |
tblPerson.Suffix |
suffix |
688-697 |
|
|
Person Salutation |
tblPerson.Salutation |
salut |
698-727 |
|
|
Person SS# |
tblPerson.SSN |
ssnum |
728-736 |
Participant's social security number without hyphens |
|
fmtSSN |
738-748 |
Participant's social security number including hyphens; first 5 digits masked as "*"s if GW account set up to mask SSNs |
||
|
|
|
fmtFullName |
750-819 |
Participant's full name (Prefix + " " + FirstName + " " + LastName + Suffix) |
|
|
tblPerson.PersonAccount1 |
|
820-839 |
Participant's Person Code 1 |
|
|
tblPerson.PersonAccount2 |
|
840-859 |
Participant's Person Code 2 |
|
|
tblPerson.PersonAccount3 |
|
860-879 |
Participant's Person Code 3 |
|
|
tblPerson.PersonAccount1 |
|
880-899 |
Participant's Person Code 4 |
|
|
tblAddressType.Short |
|
900-919 |
|
|
Participant's Legal Address Line 1 |
tblAddress.Street1 |
f_legadd1* |
920-979 |
|
|
Participant's Legal Address Line 2 |
tblAddress.Street2 |
f_legadd2* |
980-1039 |
|
|
Participant's Legal City |
tblAddress.City |
f_legcity* |
1040-1059 |
|
|
Participant's Legal State |
tblAddress.State |
f_legstate* |
1060-1061 |
|
|
Participant's Legal Zipcode |
tblAddress.Zipcode |
f_legzipcode* |
1062-1071 |
|
|
Participant's Legal Country |
tblAddress.Country |
f_legcountry* |
1072-1091 |
|
|
|
|
fmtCSZ |
1092-1171 |
Formatted city, state, and zip from participant's legal address (City + ", " + State + " " + Zipcode) |
|
Participant's Legal Address line 1 |
|
fmtLegalAddressLine1 |
1172-1241 |
Participant's first line of legal address |
|
Participant's Legal Address line 2 |
|
fmtLegalAddressLine2 |
1242-1311 |
Participant's second line of legal address |
|
Participant's Legal Address line 3 |
|
fmtLegalAddressLine3 |
1312-1381 |
Participant's third line of legal address |
|
Participant's Legal Address line 4 |
|
fmtLegalAddressLine4 |
1382-1451 |
Participant's fourth line of legal address |
|
Organization Key |
tblOrganization.OrgID |
org_pk |
1452-1461 |
|
|
Organization Nickname |
tblOrganization.OrgNickname |
orgnick |
1462-1471 |
|
|
Organization Name |
tblOrganization.OrgName |
orgname |
1472-1521 |
|
|
PIF Key |
tblPif.PifID |
pif_pk |
1522-1531 |
|
|
PIF Full Name |
tblPif.PifName |
pifname |
1532-1581 |
|
|
PIF Nickname |
tblPif.PifNickname |
pifnick |
1582-1591 |
|
|
PIF Tax ID# |
tblPif.PifTaxIDNumber |
piftaxid |
1592-1601 |
Fund tax ID# without hyphen |
|
|
|
fmtPifTaxIDNumber |
1602-1611 |
Fund tax ID# including hyphen |
|
PIF Account 1 |
tblPif.PifAccount1 |
pifaccount1 |
1614-1633 |
|
|
PIF Account 2 |
tblPif.PifAccount2 |
pifaccount2 |
1634-1653 | |
|
PIF Account 2 |
tblPif.PifAccount3 |
pifaccount3 |
1654-1673 | |
|
Fiduciary Name |
tblPif.MgrName |
mgrname |
1674-1723 |
|
|
Fund Contact |
tblPif.MgrContact |
mgrcontact |
1724-1773 |
Name of person to contact at fund; title optional |
|
Fund Contact Address Street 1 |
tblAddress.Street1 |
mgradd1 |
1774-1833 |
|
|
Fund Contact Address Street 2 |
tblAddress.Street2 |
mgradd2 |
1834-1893 |
|
|
Fund Contact City |
tblAddress.City |
mgrcity |
1894-1913 |
|
|
Fund Contact State |
tblAddress.StateCode |
mgrstate |
1914-1915 |
|
|
Fund Contact Zipcode |
tblAddress.Zipcode |
mgrzipcode |
1916-1925 |
|
|
Fund Contact Phone |
tblAddress.Phone |
mgrphone |
1926-1945 |
|
|
|
|
PifAddressLine1 |
1946-2015 |
Fund contact's first line of address |
|
|
|
PifAddressLine2 |
2016-2085 |
Fund contact's second line of address |
|
|
|
PifAddressLine3 |
2086-2155 |
Fund contact's third line of address |
|
|
|
fmtTaxYear |
2156-2159 |
Tax year entered by user |
|
|
|
fmtLetterDate |
2160-2167 |
Date of document entered by user; mmddyyyy |
* A participant's legal address is his Tax Form address, if he has one. Otherwise, it is his Home address.